package com.example.springbootecharts.controller;

import com.alibaba.fastjson.JSON;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;
import java.util.Map;

@RestController
public class Chartcontroller {
    @Autowired
    @Qualifier("jdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    @RequestMapping("/chart/workarea-money/{workarea}")
    public String chartecharts1(@PathVariable String workarea) {
        String sql = "SELECT w.workarea,ROUND(AVG(m.minmoney),2) AS avg_minmoney,ROUND(AVG(m.maxmoney),2) AS avg_maxmoney FROM jobnameworkarea w RIGHT JOIN jobnamemoney m on w.id = m.id WHERE w.job_name LIKE '%"+workarea+"%' GROUP BY w.workarea ORDER BY m.avg_maxmoney DESC LIMIT 10;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping("/chart/experience-money/{experience}")
    public String chartecharts2(@PathVariable String experience) {
        String sql = "SELECT e.experience, ROUND(AVG(m.minmoney),2) AS avg_minmoney,ROUND(AVG(m.avgmoney),2) AS avg_avgmoney,ROUND(AVG(m.maxmoney),2) AS avg_maxmoney FROM experienceeducation e RIGHT JOIN jobnamemoney m on e.id = m.id WHERE e.job_name LIKE '%"+experience+"%' GROUP BY e.experience ORDER BY m.avg_avgmoney ASC;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping("/chart/education-money/{education}")
    public String chartecharts3(@PathVariable String education) {
        String sql = "SELECT e.education,ROUND(AVG(m.minmoney),2) AS avg_minmoney,ROUND(AVG(m.avgmoney),2) AS avg_avgmoney,ROUND(AVG(m.maxmoney),2) AS avg_maxmoney FROM experienceeducation e RIGHT JOIN jobnamemoney m on e.id = m.id WHERE e.job_name LIKE '%"+education+"%' GROUP BY e.education ORDER BY e.avg_avgmoney ASC;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

    @RequestMapping("/chart/education/{education}")
    public String chartecharts4(@PathVariable String education) {
        String sql = "SELECT education AS name,COUNT(education) AS value FROM experienceeducation WHERE job_name LIKE '%"+education+"%' GROUP BY education ORDER BY value DESC;";
        List<Map<String, Object>> list = jdbcTemplate.queryForList(sql);
        return JSON.toJSONString(list);
    }

}


